<?php
/**
* @file $Id: CalcGPA.php 523 2007-05-17 17:15:45Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

$db_date = DBDate();
$user_school = UserSchool();
$user_syear = UserSyear();

DBQuery("
		DELETE FROM  student_gpa_calculated 
        WHERE        1 = 1 
		");
		
DBQuery("
		INSERT INTO  student_gpa_calculated
		(  student_id
		 , gpa
		 , weighted_gpa
		) ( SELECT      g.student_id
			 ,          sum(g.GPA_POINTS) / sum(g.CREDITS) AS GPA
			 ,          sum(g.WEIGHTED_GPA_POINTS) / sum(g.CREDITS) AS WEIGHTED_GPA 
			FROM        student_report_card_grades g 
			WHERE       g.CREDITS > 0 
			 AND        g.AFFECTS_GPA = 'Y' 
			 AND        g.school_id = $user_school
			GROUP BY    g.student_id
			HAVING      sum(g.CREDITS) > 0 
		)");

$gpa_RET = DBGet(DBQuery("
		SELECT       ssm.STUDENT_ID
		 ,           ssm.GRADE_ID
		 ,           COALESCE(sum(g.GPA_POINTS) / sum(g.CREDITS), 0) AS GPA
		 ,           COALESCE(sum(g.WEIGHTED_GPA_POINTS) / sum(g.CREDITS), 0) AS WEIGHTED_GPA
		 ,           sum("
						.db_case(array("g.CARRIES_CREDITS", "'Y'", 'g.CREDITS', "'0'"))
						.") AS CREDITS 
		 ,           NULL as class_rank
		FROM         STUDENT_ENROLLMENT ssm 
		 LEFT JOIN   STUDENT_REPORT_CARD_GRADES g 
		  ON         g.STUDENT_ID = ssm.STUDENT_ID 
		WHERE        ssm.SYEAR = '$user_syear' 
		 AND         g.CREDITS > 0 
		 AND         g.AFFECTS_GPA = 'Y' 
		 AND         ssm.INCLUDE_IN_CLASS_RANK = 'Y'
		GROUP BY     ssm.STUDENT_ID
		 ,           ssm.GRADE_ID
		 ,           ssm.INCLUDE_IN_CLASS_RANK 
		HAVING       sum(g.CREDITS) > 0 
		ORDER BY     COALESCE(sum(g.GPA_POINTS) / sum(g.CREDITS), 0) DESC
		 ,           sum(g.CREDITS) DESC
		"), array(), array('GRADE_ID'));
//		 AND       ( '$db_date' <= ssm.END_DATE 
//		  OR         ssm.END_DATE IS NULL ) 
		   
if (count($gpa_RET)) {
	foreach($gpa_RET as $grade) {
		$i = 1;
		$prev_gpa = '';
		$grade_total = count($grade);
		$tie_count = 0;

		foreach($grade as $student) {
		    $student['GPA'] = round($student['GPA'], 3);
		    $student['WEIGHTED_GPA'] = round($student['WEIGHTED_GPA'], 3);

			if ($prev_gpa != $student['GPA']) {
				$i += $tie_count;
				$tie_count = 0;
			}
			
			$prev_gpa = $student['GPA'];
			$tie_count++;

			$student['CLASS_RANK'] = $i;
				
			DBQuery("
					UPDATE      STUDENT_GPA_CALCULATED 
					SET         CLASS_RANK = '$student[CLASS_RANK]'
					 ,          CUMULATIVE_GPA = '$student[GPA]'
					 ,          CUMULATIVE_WEIGHTED_GPA = '$student[WEIGHTED_GPA]'
					 ,          GPA = '$student[GPA]'
					 ,          WEIGHTED_GPA = '$student[WEIGHTED_GPA]'
					 ,          GRADE_TOTAL = '$grade_total'
					 ,          CUMULATIVE_CREDITS = '$student[CREDITS]' 
					WHERE       STUDENT_ID = '$student[STUDENT_ID]' 
					");
		}
	}
	echo '<b>done</b>';
}
?>